﻿using System;
using System.Data; 
using System.Data.OleDb;
using System.Data.SqlClient;



namespace DAO
{
    public class DataTransformationService
    {
        #region Private members
        private string _provider = "Provider=Microsoft.Jet.OLEDB.4.0;";
        private string _dataSourceString = "Data Source="; private string _dataSourceType = ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1;ReadOnly:=false;UpdateLinks:=0\"";
        private string _fileName;
        #endregion
        public DataTransformationService()
        { // // TODO: Add constructor logic here // 
        }
        public string FileName
        {
            get { return _fileName; }
            set { _fileName = value; }
        }
        public string ConnectionString
        {
            get
            {
                if (FileName == "")
                    throw new NoNullAllowedException("File name must be required");
                return _provider + _dataSourceString + FileName + _dataSourceType;
            }
        } /// <summary> /// Trả về các bảng(table)/sheet trong file DataSource /// </summary> /// <returns></returns> 
        public DataTable GetTableNames()
        {
            DataTable dtSheets = null;
            using (OleDbConnection oledbConn = new OleDbConnection(ConnectionString))
            {
                try
                {
                    oledbConn.Open(); //Trả về các sheet có trong file excel 
                    dtSheets = oledbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                }
                catch (Exception exc)
                {
                    throw new Exception(exc.Message);
                }
                finally
                {
                    if (oledbConn != null)
                        oledbConn.Close();
                }
            }
            return dtSheets;
        }
        public DataSet GetDataImport(string tableName)
        {
            DataSet dsDataImport = new DataSet();
            bool isValid = false;
            foreach (DataRow r in GetTableNames().Rows)
            {
                if (r[2].ToString() == tableName)
                {
                    isValid = true; break;
                }
            } if (!isValid)
            {
                throw new Exception("Tên Table/Sheet không tồn tại trong file.");
            } string selectCommand = "select * from [" + tableName + "]";
            using (OleDbConnection oledbConn = new OleDbConnection(ConnectionString))
            {
                try
                {
                    oledbConn.Open();
                    //Mở dữ liệu trong sheet được chọn và đổ vào DataSet 
                    OleDbDataAdapter oleDataAdapter = new OleDbDataAdapter(selectCommand, oledbConn);
                    oleDataAdapter.Fill(dsDataImport);
                    dsDataImport.Tables[0].TableName = "CoDong"; oleDataAdapter.Dispose();
                }
                catch (Exception exc)
                {
                    throw new Exception(exc.Message);
                }
                finally
                {
                    if (oledbConn != null)
                        oledbConn.Close();
                }
            }
            return dsDataImport;
        }
    }
}
